#!/usr/bin/env python
# -*- coding:utf-8 -*-
# Build-in / Std

'''
Created on 2016年9月18日

Just import data before 2015/12(not include) from original.xls

@author: Hawk
'''
import xlrd
from Model import *

_excel_name_ = 'original.xls'
fixed_headers = ['Type', 'Part Number', 'Products', 'Region', '2012 Qty', '2013 Qty', '2014 Qty', '2015 Qty', 'Total Qty', 'MM', 'Q/M', 'MP date', 'MP MM']
year_labels = [u'2011', u'2012', u'2013', u'2014', u'2015']
year_label_2016 = [u'2016']
month_labels = [u'1', u'2', u'3', u'4', u'5', u'6', u'7', u'8', u'9', u'10', u'11', u'12']
year_row_idx = 0
month_row_idx = 1
fixed_headers_len = len(fixed_headers)

def get_products(table, row_idx):
    def get_project(table, row_idx):
        project_name = str(table.cell(row_idx, fixed_headers.index('Products')).value).strip()
        return Project.select().where(Project.name == project_name).get().id
    def get_sku(table, row_idx):
        sku_name = str(table.cell(row_idx, fixed_headers.index('Region')).value).strip()
        return Sku.select().where(Sku.name == sku_name).get().id

    #project_name = str(table.cell(row_idx, fixed_headers.index('Products')).value).strip()
    #sku_name = str(table.cell(row_idx, fixed_headers.index('Region')).value).strip()
    #print project_name, sku_name
    project_id = get_project(table, row_idx)
    sku_id = get_sku(table, row_idx)
    return Product.select().where((Product.project == project_id) & (Product.sku == sku_id)).get()

def get_product_mm(table, row_idx):
    try:
        return float(str(table.cell(row_idx, fixed_headers.index('MM')).value).strip())
    except:
        return 0

def get_cell_type(excel_file, curr_cell):
    # Cell type
    # 0. EMPTY
    # 1. FILL GREEN, Sync WW
    # 2. FILL YELLOW, No Sync
    # 3. FILL RED, MP
    # 4. FILL GRAY, Phase out

    #[(fill_pattern, pattern_colour_index),]
    types = [(0, 0), (1, 17), (1, 13)]

    if curr_cell.xf_index is not None:
        fmt = excel_file.xf_list[curr_cell.xf_index]
        cell_style = (fmt.background.fill_pattern, fmt.background.pattern_colour_index)
        if cell_style in types:
            return types.index(cell_style)
    return 0

def get_project_id_by_name(name):
    return Project.select().where(Project.name == name).get().id
def get_sku_id_by_name(name):
    return Sku.select().where(Sku.name == name).get().id
def get_product_id_by_name(project_name, sku_name):
    project_id = get_project_id_by_name(project_name)
    sku_id = get_sku_id_by_name(sku_name)
    return Product.select().where((Product.project == project_id) & (Product.sku == sku_id)).get().id


if __name__ == '__main__':
    db_name = 'model_data.db'
    database = SqliteDatabaseWarpper(db_name)

    database.register(Category)
    database.register(Project)
    database.register(Sku)
    database.register(Record)
    database.register(Product)
    database.register(Operation)

    database.connect()

    excel_file = xlrd.open_workbook(_excel_name_, formatting_info=True)
    table = excel_file.sheet_by_index(0)
    print table.cell_note_map[(0, 0)].text
    print table.nrows
    print table.ncols
    records = []
    for row_idx in range(2, table.nrows):
        #print get_products(table, row_idx).id
        product_id = get_products(table, row_idx).id
        product_release_count = 0
        product_mm = get_product_mm(table, row_idx)

        for col_idx in range(len(year_labels) * 12):
            curr_cell = table.cell(row_idx, fixed_headers_len + col_idx)
            cell_type = get_cell_type(excel_file, curr_cell)

            product_release_count = product_release_count + (int(curr_cell.value) if cell_type != 0 else 0)

        mm_per_release = []
        if product_release_count != 0:
            avg_mm = product_mm / product_release_count
            avg_mm_last = avg_mm
            avg_mm_str = str(avg_mm)

            if avg_mm_str.find('.') != -1:

                if product_release_count == 1:
                    mm_per_release.append("{0:.2f}".format(round(avg_mm, 2)))
                elif len(avg_mm_str[avg_mm_str.find('.') + 1:]) <= 2:
                    for i in range(product_release_count):
                        mm_per_release.append("{0:.2f}".format(round(avg_mm, 2)))
                elif len(avg_mm_str[avg_mm_str.find('.') + 1:]) > 2:
                    avg_mm_str = avg_mm_str[0:avg_mm_str.find('.') + 3]
                    avg_mm = float(avg_mm_str)

                    avg_mm_last = product_mm - avg_mm * (product_release_count - 1)

                    for i in range(product_release_count - 1):
                        mm_per_release.append("{0:.2f}".format(round(avg_mm, 2)))
                    mm_per_release.append("{0:.2f}".format(round(avg_mm_last, 2)))
            else:
                for i in range(product_release_count):
                    mm_per_release.append("{0:.2f}".format(round(avg_mm, 2)))

        product_ = get_products(table, row_idx)
        print '', product_.project.name, product_.sku.name, product_mm, product_release_count, len(mm_per_release), mm_per_release

        index_of_release = 0
        for col_idx in range(len(year_labels) * 12):
            curr_cell = table.cell(row_idx, fixed_headers_len + col_idx)
            cell_type = get_cell_type(excel_file, curr_cell)

            if cell_type != 0:
                #print curr_cell.value, type(curr_cell.value), cell_type
                for i in range(int(curr_cell.value)):
                    record = {}
                    year = int(year_labels[col_idx / len(month_labels)])
                    month = int(month_labels[col_idx % len(month_labels)])
                    record['release_date'] = datetime.date(year, month, i + 1)
                    record['product'] = product_id
                    record['version'] = '1.00'
                    record['ww_version'] = '1.00'
                    record['development_mm'] = 0.0
                    record['maintenance_mm'] = mm_per_release[index_of_release]
                    record['is_sync_ww'] = True if cell_type == 1 else False
                    records.append(record)
                    index_of_release = index_of_release + 1

    print 'len(records)', len(records)

    # append data of 2015/12
    records_of_201512 = [
                    {'release_date': datetime.date(2015, 12, 3), 'product': get_product_id_by_name('Fenix 3', 'CHN'), 'version': '5.10', 'maintenance_mm': 0.125, 'is_sync_ww': True, 'ww_version': '5.10', 'development_mm': 0.0, },
                    {'release_date': datetime.date(2015, 12, 3), 'product': get_product_id_by_name('Fenix 3', 'TWN'), 'version': '5.10', 'maintenance_mm': 0.125, 'is_sync_ww': True, 'ww_version': '5.10', 'development_mm': 0.0, },
                    {'release_date': datetime.date(2015, 12, 3), 'product': get_product_id_by_name('Fenix 3', 'JPN'), 'version': '5.10', 'maintenance_mm': 0.125, 'is_sync_ww': True, 'ww_version': '5.10', 'development_mm': 0.0, },
                    {'release_date': datetime.date(2015, 12, 3), 'product': get_product_id_by_name('Fenix 3', 'SEA'), 'version': '5.10', 'maintenance_mm': 0.125, 'is_sync_ww': True, 'ww_version': '5.10', 'development_mm': 0.0, },
                    {'release_date': datetime.date(2015, 12, 3), 'product': get_product_id_by_name('Fenix 3', 'KOR'), 'version': '5.10', 'maintenance_mm': 0.125, 'is_sync_ww': True, 'ww_version': '5.10', 'development_mm': 0.0, },
                    ]
    records.extend(records_of_201512)
    print 'len(records)', len(records)
    # read data of 2016
    fixed_headers_len_before_2016 = fixed_headers_len + len(year_labels) * 12
    for row_idx in range(2, table.nrows):
        product = get_products(table, row_idx)
        product_id = product.id

        record_for_mp_date = {}
        record_for_mp_date['release_date'] = product.mp_date
        record_for_mp_date['product'] = product_id
        record_for_mp_date['version'] = '2.00'
        record_for_mp_date['ww_version'] = '2.00'
        def get_product_mp_mm(table, row_idx):
            curr_cell = table.cell(row_idx, fixed_headers.index('MP MM'))
            return float(curr_cell.value) if cmp(curr_cell.value, '') != 0 else 0

        record_for_mp_date['development_mm'] = get_product_mp_mm(table, row_idx)
        record_for_mp_date['maintenance_mm'] = 0.0
        record_for_mp_date['is_sync_ww'] = False
        record_for_mp_date['is_mp'] = True

        records.append(record_for_mp_date)

        for col_idx in range(len(year_label_2016) * 12):
            cell_row_col = (row_idx, fixed_headers_len_before_2016 + col_idx)
            curr_cell = table.cell(row_idx, fixed_headers_len_before_2016 + col_idx)
            cell_type = get_cell_type(excel_file, curr_cell)

            if cell_row_col in table.cell_note_map:
                for line in table.cell_note_map[cell_row_col].text.split('\n')[1:]:
                    parts = line.split()
                    date_of_release = parts[0].strip()
                    version_of_release = parts[1].strip()
                    mm_of_release = parts[2].strip()

                    record = {}
                    year = int(date_of_release.split('/')[0])
                    month = int(date_of_release.split('/')[1])
                    day = int(date_of_release.split('/')[2])
                    record['release_date'] = datetime.date(year, month, day)
                    record['product'] = product_id
                    record['version'] = version_of_release
                    record['ww_version'] = version_of_release
                    record['development_mm'] = 0.0
                    record['maintenance_mm'] = mm_of_release
                    record['is_sync_ww'] = True if cell_type == 1 else False
                    records.append(record)
    print 'len(records)', len(records)

    for r in records:
        rec = Record()
        rec.date = r['release_date'];
        rec.product = r['product']
        rec.version = r['version'];
        rec.ww_version = r['ww_version'];
        rec.development_mm = r['development_mm'];
        rec.maintenance_mm = r['maintenance_mm'];
        rec.is_sync_ww = r['is_sync_ww'];
        rec.is_mp = r['is_mp'] if 'is_mp' in r else False

        ex_record = Record.getOne((Record.date == rec.date) & (Record.product == rec.product) & (Record.version == rec.version))
        if ex_record is None:
            rec.save()
        else:
            raise ValueError('Have you released [%s %s %s] on [%s] twice?' % (ex_record.product.project.name, ex_record.product.sku.name, ex_record.version, rec.date))

    print 'data parse done!'
    exit(0)
    cell = table.cell(3, 13)
    print cell.value
    print "cell.xf_index is", cell.xf_index
    fmt = excel_file.xf_list[cell.xf_index]
    print "type(fmt) is", type(fmt)
    print
    print "fmt.dump():"
    #fmt.dump()
    print 'background_colour_index', fmt.background.background_colour_index
    print 'fill_pattern', fmt.background.fill_pattern
    print 'pattern_colour_index', fmt.background.pattern_colour_index

    cell = table.cell(3, 14)
    print cell.value
    print "cell.xf_index is", cell.xf_index
    fmt = excel_file.xf_list[cell.xf_index]
    print "type(fmt) is", type(fmt)
    print
    print "fmt.dump():"
    #fmt.dump()
    print 'background_colour_index', fmt.background.background_colour_index
    print 'fill_pattern', fmt.background.fill_pattern
    print 'pattern_colour_index', fmt.background.pattern_colour_index
